"""
文件名: category_service.py
描述: 频道分类管理服务

本模块包含以下主要功能：
1. 分类的CRUD操作
2. 频道分类关联管理
3. 用户默认分类初始化
4. 分类排序和批量操作
5. 频道分配和移动功能

依赖模块:
   - sqlalchemy: 数据库操作
   - datetime: 时间处理
   - typing: 类型注解
   - uuid: 唯一标识符

使用示例:
   >>> category_service = CategoryService()
   >>> categories = await category_service.get_user_categories(user_id)
   >>> await category_service.create_category(user_id, category_data)
   >>> await category_service.assign_channel_to_category(channel_id, category_id)

注意事项:
   - 分类名称在用户范围内唯一
   - 默认分类不能删除
   - 频道可以属于多个分类
   - 批量操作要考虑性能
   - 要处理并发更新冲突

作者: AI助手
创建日期: 2024-06-27
最后修改: 2024-06-27
版本: 1.0.0
"""

from datetime import datetime
from typing import Dict, List, Optional, Tuple, Any
import uuid

from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, update, delete, func, and_, or_, insert
from sqlalchemy.orm import selectinload, joinedload

from src.core.database import get_db_session
from src.core.redis import redis_manager
from src.core.logger import get_logger
from src.models.category import (
    Category, 
    CategoryCreate, 
    CategoryUpdate, 
    CategoryInDB,
    CategorySummary,
    UserChannel,
    UserChannelCreate,
    UserChannelUpdate,
    channel_category_association,
    DEFAULT_CATEGORIES,
)
from src.models.channel import Channel
from src.models.user import User
from src.utils.exceptions import (
    DatabaseException,
    ValidationException,
    AuthorizationException,
)

logger = get_logger(__name__)


class CategoryService:
    """频道分类管理服务类"""
    
    def __init__(self):
        """初始化分类服务"""
        self.redis = redis_manager
    
    async def get_user_categories(
        self, 
        user_id: uuid.UUID,
        include_channel_count: bool = True,
        only_active: bool = True,
    ) -> List[CategoryInDB]:
        """
        获取用户的所有分类
        
        Args:
            user_id: 用户ID
            include_channel_count: 是否包含频道数量
            only_active: 是否只返回激活的分类
            
        Returns:
            List[CategoryInDB]: 分类列表
        """
        try:
            async with get_db_session() as db:
                # 构建查询
                stmt = select(Category).where(
                    Category.user_id == user_id,
                    Category.deleted_at.is_(None)
                )
                
                if only_active:
                    stmt = stmt.where(Category.is_active == True)
                
                # 按排序顺序和创建时间排序
                stmt = stmt.order_by(Category.sort_order, Category.created_at)
                
                result = await db.execute(stmt)
                categories = result.scalars().all()
                
                # 如果需要包含频道数量，更新统计信息
                if include_channel_count:
                    await self._update_category_channel_counts(db, [cat.id for cat in categories])
                    # 重新查询以获取更新后的数据
                    result = await db.execute(stmt)
                    categories = result.scalars().all()
                
                category_list = [CategoryInDB.from_orm(cat) for cat in categories]
                
                logger.debug(f"获取用户分类成功", user_id=str(user_id), count=len(category_list))
                return category_list
                
        except Exception as e:
            logger.error(f"获取用户分类失败: {e}", user_id=str(user_id))
            raise DatabaseException(f"获取分类失败: {str(e)}")
    
    async def get_category_by_id(
        self, 
        category_id: uuid.UUID, 
        user_id: Optional[uuid.UUID] = None
    ) -> Optional[CategoryInDB]:
        """
        根据ID获取分类
        
        Args:
            category_id: 分类ID
            user_id: 用户ID（可选，用于权限验证）
            
        Returns:
            Optional[CategoryInDB]: 分类对象，未找到返回None
        """
        try:
            async with get_db_session() as db:
                stmt = select(Category).where(
                    Category.id == category_id,
                    Category.deleted_at.is_(None)
                )
                
                if user_id:
                    stmt = stmt.where(Category.user_id == user_id)
                
                result = await db.execute(stmt)
                category = result.scalar_one_or_none()
                
                if not category:
                    return None
                
                # 更新频道数量
                await self._update_category_channel_counts(db, [category.id])
                await db.refresh(category)
                
                return CategoryInDB.from_orm(category)
                
        except Exception as e:
            logger.error(f"根据ID获取分类失败: {e}", category_id=str(category_id))
            raise DatabaseException(f"获取分类失败: {str(e)}")
    
    async def create_category(
        self, 
        user_id: uuid.UUID, 
        category_data: CategoryCreate
    ) -> CategoryInDB:
        """
        创建新分类
        
        Args:
            user_id: 用户ID
            category_data: 分类创建数据
            
        Returns:
            CategoryInDB: 创建的分类对象
        """
        try:
            async with get_db_session() as db:
                # 检查分类名称是否已存在
                existing_stmt = select(Category).where(
                    Category.user_id == user_id,
                    Category.name == category_data.name,
                    Category.deleted_at.is_(None)
                )
                existing_result = await db.execute(existing_stmt)
                if existing_result.scalar_one_or_none():
                    raise ValidationException(f"分类名称已存在: {category_data.name}")
                
                # 设置排序顺序（如果未指定）
                if category_data.sort_order == 0:
                    max_order_stmt = select(func.max(Category.sort_order)).where(
                        Category.user_id == user_id,
                        Category.deleted_at.is_(None)
                    )
                    max_order_result = await db.execute(max_order_stmt)
                    max_order = max_order_result.scalar() or 0
                    category_data.sort_order = max_order + 1
                
                # 创建分类
                category_dict = category_data.dict()
                category_dict["user_id"] = user_id
                
                category = Category(**category_dict)
                category.created_at = datetime.utcnow()
                category.updated_at = datetime.utcnow()
                
                db.add(category)
                await db.commit()
                await db.refresh(category)
                
                # 更新用户分类计数
                await self._update_user_category_count(db, user_id)
                
                logger.info(f"分类创建成功", category_id=str(category.id), name=category.name)
                return CategoryInDB.from_orm(category)
                
        except ValidationException:
            raise
        except Exception as e:
            logger.error(f"创建分类失败: {e}", user_id=str(user_id), category_data=category_data.dict())
            raise DatabaseException(f"创建分类失败: {str(e)}")
    
    async def update_category(
        self, 
        category_id: uuid.UUID, 
        user_id: uuid.UUID,
        category_data: CategoryUpdate
    ) -> Optional[CategoryInDB]:
        """
        更新分类信息
        
        Args:
            category_id: 分类ID
            user_id: 用户ID
            category_data: 更新数据
            
        Returns:
            Optional[CategoryInDB]: 更新后的分类对象，未找到返回None
        """
        try:
            async with get_db_session() as db:
                # 查找分类
                stmt = select(Category).where(
                    Category.id == category_id,
                    Category.user_id == user_id,
                    Category.deleted_at.is_(None)
                )
                result = await db.execute(stmt)
                category = result.scalar_one_or_none()
                
                if not category:
                    return None
                
                # 检查分类名称唯一性（如果要更新名称）
                if category_data.name and category_data.name != category.name:
                    existing_stmt = select(Category).where(
                        Category.user_id == user_id,
                        Category.name == category_data.name,
                        Category.id != category_id,
                        Category.deleted_at.is_(None)
                    )
                    existing_result = await db.execute(existing_stmt)
                    if existing_result.scalar_one_or_none():
                        raise ValidationException(f"分类名称已存在: {category_data.name}")
                
                # 更新字段
                update_dict = category_data.dict(exclude_unset=True)
                for field, value in update_dict.items():
                    if hasattr(category, field) and value is not None:
                        setattr(category, field, value)
                
                category.updated_at = datetime.utcnow()
                
                await db.commit()
                await db.refresh(category)
                
                logger.info(f"分类更新成功", category_id=str(category.id))
                return CategoryInDB.from_orm(category)
                
        except ValidationException:
            raise
        except Exception as e:
            logger.error(f"更新分类失败: {e}", category_id=str(category_id))
            raise DatabaseException(f"更新分类失败: {str(e)}")
    
    async def delete_category(
        self, 
        category_id: uuid.UUID, 
        user_id: uuid.UUID,
        force: bool = False
    ) -> bool:
        """
        删除分类
        
        Args:
            category_id: 分类ID
            user_id: 用户ID
            force: 是否强制删除（删除默认分类）
            
        Returns:
            bool: 是否删除成功
        """
        try:
            async with get_db_session() as db:
                # 查找分类
                stmt = select(Category).where(
                    Category.id == category_id,
                    Category.user_id == user_id,
                    Category.deleted_at.is_(None)
                )
                result = await db.execute(stmt)
                category = result.scalar_one_or_none()
                
                if not category:
                    return False
                
                # 检查是否为默认分类
                if category.is_default and not force:
                    raise ValidationException("不能删除默认分类")
                
                # 软删除分类
                category.deleted_at = datetime.utcnow()
                category.is_active = False
                category.updated_at = datetime.utcnow()
                
                # 移除分类与频道的关联
                delete_associations_stmt = delete(channel_category_association).where(
                    channel_category_association.c.category_id == category_id
                )
                await db.execute(delete_associations_stmt)
                
                await db.commit()
                
                # 更新用户分类计数
                await self._update_user_category_count(db, user_id)
                
                logger.info(f"分类删除成功", category_id=str(category_id))
                return True
                
        except ValidationException:
            raise
        except Exception as e:
            logger.error(f"删除分类失败: {e}", category_id=str(category_id))
            raise DatabaseException(f"删除分类失败: {str(e)}")
    
    async def assign_channel_to_category(
        self,
        channel_id: uuid.UUID,
        category_id: uuid.UUID,
        user_id: uuid.UUID
    ) -> bool:
        """
        将频道分配到分类
        
        Args:
            channel_id: 频道ID
            category_id: 分类ID
            user_id: 用户ID
            
        Returns:
            bool: 是否分配成功
        """
        try:
            async with get_db_session() as db:
                # 验证分类属于该用户
                category_stmt = select(Category).where(
                    Category.id == category_id,
                    Category.user_id == user_id,
                    Category.deleted_at.is_(None)
                )
                category_result = await db.execute(category_stmt)
                if not category_result.scalar_one_or_none():
                    raise AuthorizationException("分类不存在或无权限访问")
                
                # 验证频道存在
                channel_stmt = select(Channel).where(
                    Channel.id == channel_id,
                    Channel.deleted_at.is_(None)
                )
                channel_result = await db.execute(channel_stmt)
                if not channel_result.scalar_one_or_none():
                    raise ValidationException("频道不存在")
                
                # 检查关联是否已存在
                existing_stmt = select(channel_category_association).where(
                    and_(
                        channel_category_association.c.channel_id == channel_id,
                        channel_category_association.c.category_id == category_id
                    )
                )
                existing_result = await db.execute(existing_stmt)
                if existing_result.fetchone():
                    logger.debug(f"频道已在该分类中", channel_id=str(channel_id), category_id=str(category_id))
                    return True
                
                # 创建关联
                insert_stmt = insert(channel_category_association).values(
                    channel_id=channel_id,
                    category_id=category_id,
                    created_at=datetime.utcnow()
                )
                await db.execute(insert_stmt)
                
                # 更新分类频道计数
                await self._update_category_channel_counts(db, [category_id])
                
                await db.commit()
                
                logger.info(f"频道分配到分类成功", channel_id=str(channel_id), category_id=str(category_id))
                return True
                
        except (ValidationException, AuthorizationException):
            raise
        except Exception as e:
            logger.error(f"分配频道到分类失败: {e}", channel_id=str(channel_id), category_id=str(category_id))
            raise DatabaseException(f"分配频道失败: {str(e)}")
    
    async def remove_channel_from_category(
        self,
        channel_id: uuid.UUID,
        category_id: uuid.UUID,
        user_id: uuid.UUID
    ) -> bool:
        """
        从分类中移除频道
        
        Args:
            channel_id: 频道ID
            category_id: 分类ID
            user_id: 用户ID
            
        Returns:
            bool: 是否移除成功
        """
        try:
            async with get_db_session() as db:
                # 验证分类属于该用户
                category_stmt = select(Category).where(
                    Category.id == category_id,
                    Category.user_id == user_id,
                    Category.deleted_at.is_(None)
                )
                category_result = await db.execute(category_stmt)
                if not category_result.scalar_one_or_none():
                    raise AuthorizationException("分类不存在或无权限访问")
                
                # 删除关联
                delete_stmt = delete(channel_category_association).where(
                    and_(
                        channel_category_association.c.channel_id == channel_id,
                        channel_category_association.c.category_id == category_id
                    )
                )
                result = await db.execute(delete_stmt)
                
                # 更新分类频道计数
                await self._update_category_channel_counts(db, [category_id])
                
                await db.commit()
                
                success = result.rowcount > 0
                if success:
                    logger.info(f"从分类移除频道成功", channel_id=str(channel_id), category_id=str(category_id))
                else:
                    logger.debug(f"频道不在该分类中", channel_id=str(channel_id), category_id=str(category_id))
                
                return success
                
        except AuthorizationException:
            raise
        except Exception as e:
            logger.error(f"从分类移除频道失败: {e}", channel_id=str(channel_id), category_id=str(category_id))
            raise DatabaseException(f"移除频道失败: {str(e)}")
    
    async def get_category_channels(
        self,
        category_id: uuid.UUID,
        user_id: uuid.UUID,
        limit: int = 50,
        offset: int = 0
    ) -> Tuple[List[Dict[str, Any]], int]:
        """
        获取分类下的频道列表
        
        Args:
            category_id: 分类ID
            user_id: 用户ID
            limit: 每页数量
            offset: 偏移量
            
        Returns:
            Tuple[List[Dict[str, Any]], int]: (频道列表, 总数)
        """
        try:
            async with get_db_session() as db:
                # 验证分类权限
                category_stmt = select(Category).where(
                    Category.id == category_id,
                    Category.user_id == user_id,
                    Category.deleted_at.is_(None)
                )
                category_result = await db.execute(category_stmt)
                if not category_result.scalar_one_or_none():
                    raise AuthorizationException("分类不存在或无权限访问")
                
                # 获取频道列表
                channels_stmt = (
                    select(Channel)
                    .join(channel_category_association)
                    .where(
                        and_(
                            channel_category_association.c.category_id == category_id,
                            Channel.deleted_at.is_(None)
                        )
                    )
                    .order_by(Channel.title)
                    .limit(limit)
                    .offset(offset)
                )
                
                channels_result = await db.execute(channels_stmt)
                channels = channels_result.scalars().all()
                
                # 获取总数
                count_stmt = (
                    select(func.count(Channel.id))
                    .join(channel_category_association)
                    .where(
                        and_(
                            channel_category_association.c.category_id == category_id,
                            Channel.deleted_at.is_(None)
                        )
                    )
                )
                count_result = await db.execute(count_stmt)
                total_count = count_result.scalar()
                
                # 转换为字典格式
                channel_list = []
                for channel in channels:
                    channel_dict = {
                        "id": channel.id,
                        "channel_id": channel.channel_id,
                        "title": channel.title,
                        "thumbnail_url": channel.thumbnail_url,
                        "subscriber_count": channel.subscriber_count,
                        "video_count": channel.video_count,
                        "last_video_published_at": channel.last_video_published_at,
                        "sync_status": channel.sync_status,
                    }
                    channel_list.append(channel_dict)
                
                return channel_list, total_count
                
        except AuthorizationException:
            raise
        except Exception as e:
            logger.error(f"获取分类频道列表失败: {e}", category_id=str(category_id))
            raise DatabaseException(f"获取频道列表失败: {str(e)}")
    
    async def batch_assign_channels(
        self,
        channel_ids: List[uuid.UUID],
        category_id: uuid.UUID,
        user_id: uuid.UUID
    ) -> Dict[str, int]:
        """
        批量分配频道到分类
        
        Args:
            channel_ids: 频道ID列表
            category_id: 分类ID
            user_id: 用户ID
            
        Returns:
            Dict[str, int]: 操作结果统计
        """
        try:
            async with get_db_session() as db:
                # 验证分类权限
                category_stmt = select(Category).where(
                    Category.id == category_id,
                    Category.user_id == user_id,
                    Category.deleted_at.is_(None)
                )
                category_result = await db.execute(category_stmt)
                if not category_result.scalar_one_or_none():
                    raise AuthorizationException("分类不存在或无权限访问")
                
                stats = {
                    "success": 0,
                    "failed": 0,
                    "skipped": 0,  # 已存在的关联
                }
                
                for channel_id in channel_ids:
                    try:
                        # 验证频道存在
                        channel_stmt = select(Channel).where(
                            Channel.id == channel_id,
                            Channel.deleted_at.is_(None)
                        )
                        channel_result = await db.execute(channel_stmt)
                        if not channel_result.scalar_one_or_none():
                            stats["failed"] += 1
                            continue
                        
                        # 检查关联是否已存在
                        existing_stmt = select(channel_category_association).where(
                            and_(
                                channel_category_association.c.channel_id == channel_id,
                                channel_category_association.c.category_id == category_id
                            )
                        )
                        existing_result = await db.execute(existing_stmt)
                        if existing_result.fetchone():
                            stats["skipped"] += 1
                            continue
                        
                        # 创建关联
                        insert_stmt = insert(channel_category_association).values(
                            channel_id=channel_id,
                            category_id=category_id,
                            created_at=datetime.utcnow()
                        )
                        await db.execute(insert_stmt)
                        stats["success"] += 1
                        
                    except Exception as e:
                        logger.error(f"批量分配频道失败: {e}", channel_id=str(channel_id))
                        stats["failed"] += 1
                        continue
                
                # 更新分类频道计数
                await self._update_category_channel_counts(db, [category_id])
                
                await db.commit()
                
                logger.info(f"批量分配频道完成", category_id=str(category_id), stats=stats)
                return stats
                
        except AuthorizationException:
            raise
        except Exception as e:
            logger.error(f"批量分配频道失败: {e}", category_id=str(category_id))
            raise DatabaseException(f"批量分配频道失败: {str(e)}")
    
    async def init_default_categories(self, user_id: uuid.UUID) -> List[CategoryInDB]:
        """
        为新用户初始化默认分类
        
        Args:
            user_id: 用户ID
            
        Returns:
            List[CategoryInDB]: 创建的默认分类列表
        """
        try:
            async with get_db_session() as db:
                created_categories = []
                
                for default_cat in DEFAULT_CATEGORIES:
                    # 检查分类是否已存在
                    existing_stmt = select(Category).where(
                        Category.user_id == user_id,
                        Category.name == default_cat["name"],
                        Category.deleted_at.is_(None)
                    )
                    existing_result = await db.execute(existing_stmt)
                    if existing_result.scalar_one_or_none():
                        continue
                    
                    # 创建默认分类
                    category = Category(
                        user_id=user_id,
                        name=default_cat["name"],
                        icon=default_cat["icon"],
                        color=default_cat["color"],
                        sort_order=default_cat["sort_order"],
                        is_default=(default_cat["name"] == "未分类"),
                        created_at=datetime.utcnow(),
                        updated_at=datetime.utcnow(),
                    )
                    
                    db.add(category)
                    await db.flush()  # 获取ID
                    created_categories.append(CategoryInDB.from_orm(category))
                
                await db.commit()
                
                # 更新用户分类计数
                await self._update_user_category_count(db, user_id)
                
                logger.info(f"默认分类初始化完成", user_id=str(user_id), count=len(created_categories))
                return created_categories
                
        except Exception as e:
            logger.error(f"初始化默认分类失败: {e}", user_id=str(user_id))
            raise DatabaseException(f"初始化默认分类失败: {str(e)}")
    
    async def _update_category_channel_counts(
        self, 
        db: AsyncSession, 
        category_ids: List[uuid.UUID]
    ) -> None:
        """
        更新分类的频道计数
        
        Args:
            db: 数据库会话
            category_ids: 分类ID列表
        """
        try:
            for category_id in category_ids:
                # 计算频道数量
                count_stmt = (
                    select(func.count(channel_category_association.c.channel_id))
                    .where(channel_category_association.c.category_id == category_id)
                )
                count_result = await db.execute(count_stmt)
                channel_count = count_result.scalar() or 0
                
                # 更新分类
                update_stmt = update(Category).where(
                    Category.id == category_id
                ).values(
                    channel_count=channel_count,
                    updated_at=datetime.utcnow()
                )
                await db.execute(update_stmt)
                
        except Exception as e:
            logger.error(f"更新分类频道计数失败: {e}", category_ids=category_ids)
    
    async def _update_user_category_count(
        self, 
        db: AsyncSession, 
        user_id: uuid.UUID
    ) -> None:
        """
        更新用户的分类总数
        
        Args:
            db: 数据库会话
            user_id: 用户ID
        """
        try:
            # 计算用户分类数量
            count_stmt = select(func.count(Category.id)).where(
                Category.user_id == user_id,
                Category.deleted_at.is_(None)
            )
            count_result = await db.execute(count_stmt)
            category_count = count_result.scalar() or 0
            
            # 更新用户
            from src.models.user import User
            update_stmt = update(User).where(
                User.id == user_id
            ).values(
                total_categories=category_count,
                updated_at=datetime.utcnow()
            )
            await db.execute(update_stmt)
            
        except Exception as e:
            logger.error(f"更新用户分类计数失败: {e}", user_id=str(user_id))


# 导出主要接口
__all__ = [
    "CategoryService",
]